package dao;

import entity.User;
import tools.DBPoolHelper;

import java.sql.*;
import java.util.ArrayList;

public class UserDao {

    private String sql = "";
    private PreparedStatement pstmt;
    private ResultSet rs;
    private Connection conn = null;

    private ArrayList<User> users = new ArrayList<User>();
    private User user;

    public Connection getConn() {
        try {
            if ((conn == null) || conn.isClosed()) {
                conn = DBPoolHelper.getConnection();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    public void close() {
        try {
            if (rs != null)
                rs.close();
            if (pstmt != null)
                pstmt.close();
            if (conn != null)
                DBPoolHelper.close(conn);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            rs = null;
            pstmt = null;
            conn = null;
        }
    }

    public ArrayList<User> getByRs(ResultSet rs) {
        try {
            if (rs == null || !rs.next()) {
                return null;
            }
            users.clear();
            do {
                user = new User();
                user.setQq(rs.getInt("qq"));
                user.setNickname(rs.getString("nickname"));
                user.setSign(rs.getString("sign"));
                user.setAvatarID(rs.getString("avatarID"));
                users.add(user);
            } while (rs.next());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return users;
    }

    /**
     * 添加用户
     *
     * @param user
     * @return
     */
    public int add(User user) {
        sql = "insert into user(nickname,pwd,sign,avatarID) values(?,?,?,?)";
        int result = 0;
        try {
            pstmt = getConn().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, user.getNickname());
            pstmt.setString(2, user.getPwd());
            pstmt.setString(3, user.getSign());
            pstmt.setString(4, user.getAvatarID());
            result = pstmt.executeUpdate();
            if (result > 0) {
                ResultSet generatedKeys = pstmt.getGeneratedKeys();
                if (generatedKeys.next()) {
                    return generatedKeys.getInt(1);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return 0;
    }

    /**
     * 修改密码
     *
     * @param qq
     * @param pwd
     * @return
     */
    public Boolean updatePwdByQQ(int qq, String pwd) {
        sql = "update user set pwd = ? where qq = ?";
        int result = 0;
        try {
            pstmt = getConn().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, pwd);
            pstmt.setInt(2, qq);
            result = pstmt.executeUpdate();
            if (result > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return false;
        } finally {
            close();
        }
    }

    /**
     * 根据qq和密码获取用户对象
     *
     * @param qq
     * @param pwd
     * @return
     */
    public User getByQQAndPwd(int qq, String pwd) {
        getConn();
        sql = "select * from user where qq = ? and pwd = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, qq);
            pstmt.setString(2, pwd);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        ArrayList<User> byRs = getByRs(rs);
        if (byRs != null) {
            return byRs.get(0);
        } else {
            return null;
        }
    }

    /**
     * 根据qq号获取指定用户对象
     *
     * @param qq
     * @return
     */
    public User getByQQ(int qq) {
        getConn();
        sql = "select * from user where qq = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, qq);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        ArrayList<User> byRs = getByRs(rs);
        if (byRs != null) {
            return byRs.get(0);
        } else {
            return null;
        }
    }

    /**
     * 根据qq号获取好友列表
     *
     * @param qq
     * @return
     */
    public ArrayList<User> getFriendsByQQ(int qq) {
        getConn();
        sql = "select *\n" +
                "from user\n" +
                "where qq in (\n" +
                "    select qq\n" +
                "    from friends\n" +
                "    where qq = ? or fqq = ?\n" +
                ")\n" +
                "  and qq != ?\n" +
                "UNION\n" +
                "select *\n" +
                "from user\n" +
                "where qq in (\n" +
                "    select fqq\n" +
                "    from friends\n" +
                "    where qq = ? or fqq = ?\n" +
                ")\n" +
                "  and qq != ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, qq);
            pstmt.setInt(2, qq);
            pstmt.setInt(3, qq);
            pstmt.setInt(4, qq);
            pstmt.setInt(5, qq);
            pstmt.setInt(6, qq);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return getByRs(rs);
    }

    /**
     * 获取所有用户
     *
     * @return
     */
    public ArrayList<User> getAllUser() {
        getConn();
        sql = "select * from user";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return getByRs(rs);
    }


    /**
     * 在friend表插入一条记录
     *
     * @param qq
     * @param fqq
     * @return
     */
    public Boolean addFriend(int qq, int fqq) {
        sql = "insert into friends(qq,fqq) values(?,?)";
        int result = 0;
        try {
            pstmt = getConn().prepareStatement(sql);
            pstmt.setInt(1, qq);
            pstmt.setInt(2, fqq);
            result = pstmt.executeUpdate();
            if (result > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return false;
        } finally {
            close();
        }
    }

    public Boolean hasFriend(int qq, int fqq) {
        getConn();
        sql = "select * from friends where (qq = ? and fqq = ?) or (qq = ? and fqq = ?)";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, qq);
            pstmt.setInt(2, fqq);
            pstmt.setInt(3, fqq);
            pstmt.setInt(4, qq);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return false;
        }
    }
}
